/**  
 * @author renbangjie renbangjie@126.com  
 * @date 2016-4-25 下午2:29:24
 * @version V1.0  
 * <b>Copyright (c)</b> 2014医信金融信息服务（上海）有限公司-版权所有<br/>
 */
package com.yjd.bi.etl.user.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.time.DateUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;

import com.yjd.bi.comm.utils.DbNameUtils;
import com.yjd.bi.etl.comm.BaseService;
import com.yjd.bi.etl.comm.Constants;
import com.yjd.bi.report.util.BiUtil;
import com.yjd.bi.user.model.InvestUserStatisticsModel;
import com.yjd.comm.util.DateUtil;
import com.yjd.comm.util.FrameUtil;

/**
 * 投资用户数统计
 * 
 * @author renbangjie renbangjie@126.com
 * @date 2016-4-25 下午2:29:24
 */
@Component("investUserStatisticsService")
public class InvestUserStatisticsService extends BaseService {

	/**
	 * 插入
	 */
	private static final String SQL_INSERT_INVEST = "INSERT INTO "+DbNameUtils.get("yjd_invest_user_statistics")+" (`statistics_date`, `max_one`, `min_one_max_three`, `min_three_max_twenty`, `min_twenty_max_hundred`, `min_hundred`) VALUES (?, ?, ?, ?, ?, ?)";

	/**
	 * 查询统计最大值
	 */
	private static final String SQL_SELECT_MAX = "select ifnull(max(`statistics_date`), 0) from " + DbNameUtils.get("yjd_invest_user_statistics");

	/**
	 * 统计
	 * 
	 * @throws Exception
	 *             void
	 */
	@SuppressWarnings("unchecked")
	public void execute() throws Exception {
		Date startDate = DateUtil
				.timestampToDate(Constants.INITIALIZATION_TIME);
		Date currentDate = DateUtils
				.truncate(new Date(), Calendar.DAY_OF_MONTH);

		JdbcTemplate jdbcTemplate = BiUtil.getJdbcTemplate(this
				.getBiDatabaseInfoModel());
		int max = jdbcTemplate.queryForInt(SQL_SELECT_MAX);
		if (max > 0) {
			startDate = DateUtil.parseDate(String.valueOf(max),
					DateUtil.PATTERN_yyyyMMdd);
		}
		int betweenDays = DateUtil.getBetweenDay(startDate, currentDate);
		if (betweenDays <= 1) {
			return;
		}

		Map<Integer, InvestUserStatisticsModel> resultMap = FrameUtil
				.getBeanFieldToMap(
						getInvestUserCount(jdbcTemplate,
								FrameUtil.getTime(startDate),
								FrameUtil.getTime(currentDate)),
						"statistics_date");
		final List<InvestUserStatisticsModel> resultList = new ArrayList<InvestUserStatisticsModel>();
		InvestUserStatisticsModel investUserStatisticsModel = null;
		String day = "";
		for (int i = 1; i < betweenDays; i++) {
			day = DateUtil.format(DateUtils.addDays(startDate, i),
					DateUtil.PATTERN_yyyyMMdd);
			investUserStatisticsModel = resultMap.get(Integer.valueOf(day));
			if (investUserStatisticsModel == null) {
				investUserStatisticsModel = new InvestUserStatisticsModel(
						Integer.valueOf(day), 0, 0, 0, 0, 0);
			}
			resultList.add(investUserStatisticsModel);
		}
		// 批量插入
		jdbcTemplate.batchUpdate(SQL_INSERT_INVEST,
				new BatchPreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps, int i)
							throws SQLException {
						ps.setInt(1, resultList.get(i).getStatistics_date());
						ps.setInt(2, resultList.get(i).getMax_one());
						ps.setInt(3, resultList.get(i).getMin_one_max_three());
						ps.setInt(4, resultList.get(i)
								.getMin_three_max_twenty());
						ps.setInt(5, resultList.get(i)
								.getMin_twenty_max_hundred());
						ps.setInt(6, resultList.get(i).getMin_hundred());
					}

					@Override
					public int getBatchSize() {
						return resultList.size();
					}
				});

	}

	/**
	 * 统计用户的投资分布情况
	 * 
	 * @param jt
	 * @param args
	 * @return
	 * @throws DataAccessException
	 * @throws Exception
	 *             Map<Integer,InvestUserStatisticsModel>
	 */
	private List<InvestUserStatisticsModel> getInvestUserCount(
			JdbcTemplate jdbcTemplate, Object... args)
			throws DataAccessException, Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select ");
		sql.append("a.`addtime` as 'statistics_date',");
		sql.append("count(case when a.`price` < 10000 then a.`user_id` end) as 'max_one',");
		sql.append("count(case when 10000 < a.`price` and a.`price` < 30000 then a.`user_id` end) as 'min_one_max_three',");
		sql.append("count(case when 30000 < a.`price` and a.`price` < 200000 then a.`user_id` end) as 'min_three_max_twenty',");
		sql.append("count(case when 200000 < a.`price` and a.`price` < 1000000 then a.`user_id` end) as 'min_twenty_max_hundred',");
		sql.append("count(case when 1000000 < a.`price` then a.`user_id` end) as 'min_hundred' ");
		sql.append("from (");
		sql.append("select ");
		sql.append("from_unixtime(`addtime`, '%Y%m%d') as 'addtime',");
		sql.append("user_id,");
		sql.append("sum(`price`) as 'price'");
		sql.append("from `yjd_invest` ");
		sql.append("where ");
		sql.append("addtime >= ? and addtime < ? ");
		sql.append("group by from_unixtime(`addtime`, '%Y%m%d'), `user_id`) as a group by a.`addtime`");

		final List<InvestUserStatisticsModel> resultList = new ArrayList<InvestUserStatisticsModel>();
		jdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				resultList.add(new InvestUserStatisticsModel(rs.getInt(1), rs
						.getInt(2), rs.getInt(3), rs.getInt(4), rs.getInt(5),
						rs.getInt(6)));
			}
		}, args);

		return resultList;
	}

}
